﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.Linq;
using System.Text;
using OA.DBUtility;
using OA.Model;

namespace OA.Manager
{
    public class ProjectPayBackStatisticsManager
    {
        private SqlService _sqlService = null;
        public ProjectPayBackStatisticsManager()
        {
            _sqlService = new SqlService();
        }

        public List<ProjectPayBackStatistics> GetSearchPaged(ProjectPayBackStatistics info, int pageIndex, int pageSize, ref int total)
        {
            string search = "";
            if (info != null)
                search = " projectId like '%" + info.ProjectNum +
                         "%' or ProjectId in (select ProjectNum from Project where ProjectName like '%" + info.ProjectName +
                         "%')";
            DataTable dt = new SqlService<ProjectPayBackInfo>().GetPaged("ProjectId", search, pageIndex, pageSize, ref pageSize, ref total);
            if (dt.Rows.Count <= 0)
                return null;
            List<ProjectPayBackStatistics> list = new List<ProjectPayBackStatistics>();
            SqlParameter spProjectNum = new SqlParameter("ProjectNum", DBNull.Value) { DbType = DbType.String };
            SqlParameter spProjectName = new SqlParameter("ProjectName", DBNull.Value) { DbType = DbType.String, Direction = ParameterDirection.Output };
            SqlParameter spSumContractAmount = new SqlParameter("SumContractAmount", 0.0000)
            {
                DbType = DbType.Decimal,
                Direction = ParameterDirection.Output
            };
            SqlParameter spSumApplyAmount = new SqlParameter("@SumApplyAmount", 0.0000) { DbType = DbType.Decimal, Direction = ParameterDirection.Output };
            SqlParameter spSumBillAmount = new SqlParameter("SumBillAmount", 0.0000) { DbType = DbType.Decimal, Direction = ParameterDirection.Output };
            SqlParameter spSumCashAmount = new SqlParameter("SumCashAmount", 0.0000) { DbType = DbType.Decimal, Direction = ParameterDirection.Output };
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                string projectNum = dt.Rows[i]["ProjectId"].ToString();
                spProjectNum.Value = projectNum;
                spSumContractAmount.Value = 0.0000;
                spSumCashAmount.Value = 0.0000;
                spProjectName.Value = 0.0000;
                spSumBillAmount.Value = 0.0000;
                ProjectPayBackStatistics model = new ProjectPayBackStatistics { ProjectNum = projectNum };
                int count = _sqlService.ExecuteNonQueryByProcedure("Proc_ProjectPayBackStatistics", spProjectNum, spProjectName, spSumContractAmount,
                                                       spSumApplyAmount, spSumBillAmount, spSumCashAmount);
                model.ProjectName = spProjectName.Value.ToString();
                model.SumContractAmount = Convert.ToDecimal(spSumContractAmount.Value);
                model.SumApplyAmount = Convert.ToDecimal(spSumApplyAmount.Value);
                model.SumBillAmount = Convert.ToDecimal(spSumBillAmount.Value);
                model.SumCashAmount = Convert.ToDecimal(spSumCashAmount.Value);
                model.ReceivableAmount = model.SumBillAmount - model.SumCashAmount;
                model.AmountCashed = model.SumContractAmount - model.SumCashAmount;
                if (model.SumCashAmount != 0)
                    model.CashScale = (model.SumCashAmount / model.SumCashAmount).ToString(CultureInfo.InvariantCulture);
                list.Add(model);
            }
            return list;

        }
        public DataTable GetSearchPaged(string projectNum, string projectName, int internalType, int pageIndex, int pageSize, ref int total)
        {
            SqlParameter spProjectNum = new SqlParameter("@ProjectNum2", projectNum) { DbType = DbType.String, Direction = ParameterDirection.Input };
            SqlParameter spProjectName = new SqlParameter("@ProjectName2", projectName) { DbType = DbType.String, Direction = ParameterDirection.Input };
            SqlParameter spIntemalType = new SqlParameter("@internalType", internalType) { DbType = DbType.String, Direction = ParameterDirection.Input };
            SqlParameter spPageIndex = new SqlParameter("@pageIndex", pageIndex) { DbType = DbType.Int32, Direction = ParameterDirection.Input };
            SqlParameter spPageSize = new SqlParameter("@pageSize", pageSize) { DbType = DbType.Int32, Direction = ParameterDirection.Input };
            SqlParameter spPageTotal = new SqlParameter("@pageTotal", DBNull.Value) { DbType = DbType.Int32, Direction = ParameterDirection.Output };
            DataTable table = _sqlService.ExecuteDataSetByProcedure("Proc_ProjectPayBackStatistics2", spProjectNum,
                                                                    spProjectName, spIntemalType, spPageIndex, spPageSize, spPageTotal).Tables[0];
            total = Convert.ToInt32(spPageTotal.Value);
            return table;
        }

        public ProjectPayBackStatistics GetStatisticsAll(int internalType)
        {
            SqlParameter spIntemalType = new SqlParameter("@internalType", internalType) { DbType = DbType.String, Direction = ParameterDirection.Input };
            SqlParameter spSumContractAmount = new SqlParameter("@SumContractAmount", 0.0000)
            {
                DbType = DbType.Decimal,
                Direction = ParameterDirection.Output
            };
            SqlParameter spSumContractEffectiveAmount = new SqlParameter("@SumContractEffectiveAmount", 0.0000)
            {
                DbType = DbType.Decimal,
                Direction = ParameterDirection.Output
            };
            SqlParameter spSumApplyAmount = new SqlParameter("@SumApplyAmount", 0.0000) { DbType = DbType.Decimal, Direction = ParameterDirection.Output };
            SqlParameter spSumBillAmount = new SqlParameter("@SumBillAmount", 0.0000) { DbType = DbType.Decimal, Direction = ParameterDirection.Output };
            SqlParameter spSumCashAmount = new SqlParameter("@SumCashAmount", 0.0000) { DbType = DbType.Decimal, Direction = ParameterDirection.Output };
            int count = _sqlService.ExecuteNonQueryByProcedure("Proc_ProjectPayBackStatisticsAll", spIntemalType, spSumContractAmount, spSumContractEffectiveAmount,
                                                     spSumApplyAmount, spSumBillAmount, spSumCashAmount);
            ProjectPayBackStatistics ppbs = new ProjectPayBackStatistics
            {
                SumContractAmount = Convert.ToInt32(spSumContractAmount.Value),
                SumContractEffectiveAmount = Convert.ToInt32(spSumContractEffectiveAmount.Value),
                SumApplyAmount = Convert.ToInt32(spSumApplyAmount.Value),
                SumBillAmount = Convert.ToInt32(spSumBillAmount.Value),
                SumCashAmount = Convert.ToInt32(spSumCashAmount.Value)
            };
            ppbs.ReceivableAmount = ppbs.SumBillAmount - ppbs.SumCashAmount;
            ppbs.AmountCashed = ppbs.SumContractAmount - ppbs.SumCashAmount;

            if (ppbs.SumContractEffectiveAmount != 0)
            {
                decimal dec = (ppbs.SumCashAmount / ppbs.SumContractEffectiveAmount) * 100;
                int lenght = dec.ToString().Length > 4 ? 4 : dec.ToString().Length;
                ppbs.CashScale = dec.ToString().Substring(0, lenght) + "%";
            }
            return ppbs;
        }
    }
}
